package com.example.computer_exam_training_sys.mapper;

import com.example.computer_exam_training_sys.entity.UserStudyNotes;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserStudyNotesMapper {
    
    @Insert("INSERT INTO user_study_notes (user_id, question_id, content, tags, is_public, create_time, update_time) " +
            "VALUES (#{userId}, #{questionId}, #{content}, #{tags}, #{isPublic}, NOW(), NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "noteId")
    int insert(UserStudyNotes note);

    @Select("SELECT n.*, u.username as user_name, q.content as question_title, " +
            "CASE WHEN n.is_public = 1 THEN true ELSE false END as is_shared " +
            "FROM user_study_notes n " +
            "LEFT JOIN users u ON n.user_id = u.user_id " +
            "LEFT JOIN questions q ON n.question_id = q.question_id " +
            "WHERE n.note_id = #{noteId}")
    UserStudyNotes findById(Long noteId);

    @Select("SELECT n.*, u.username as user_name, q.content as question_title, " +
            "CASE WHEN n.is_public = 1 THEN true ELSE false END as is_shared " +
            "FROM user_study_notes n " +
            "LEFT JOIN users u ON n.user_id = u.user_id " +
            "LEFT JOIN questions q ON n.question_id = q.question_id " +
            "WHERE n.user_id = #{userId} " +
            "ORDER BY n.create_time DESC")
    List<UserStudyNotes> findByUserId(Long userId);

    @Select("SELECT n.*, u.username as user_name, q.content as question_title, " +
            "CASE WHEN n.is_public = 1 THEN true ELSE false END as is_shared " +
            "FROM user_study_notes n " +
            "LEFT JOIN users u ON n.user_id = u.user_id " +
            "LEFT JOIN questions q ON n.question_id = q.question_id " +
            "WHERE n.question_id = #{questionId} " +
            "ORDER BY n.create_time DESC")
    List<UserStudyNotes> findByQuestionId(Long questionId);

    @Update("UPDATE user_study_notes SET content = #{content}, tags = #{tags}, " +
            "is_public = #{isPublic}, update_time = NOW() " +
            "WHERE note_id = #{noteId}")
    int update(UserStudyNotes note);

    @Delete("DELETE FROM user_study_notes WHERE note_id = #{noteId}")
    int delete(Long noteId);

    @Insert("INSERT INTO note_shares (note_id, sender_id, receiver_id, share_time) " +
            "VALUES (#{noteId}, #{senderId}, #{receiverId}, NOW())")
    int insertShareRecord(@Param("noteId") Long noteId, 
                         @Param("senderId") Long senderId, 
                         @Param("receiverId") Long receiverId);

    @Select("SELECT DISTINCT n.*, u.username as user_name, q.content as question_title, " +
            "CASE WHEN n.is_public = 1 THEN true ELSE false END as is_shared " +
            "FROM user_study_notes n " +
            "INNER JOIN user_messages m ON n.note_id = m.note_id " +
            "LEFT JOIN users u ON n.user_id = u.user_id " +
            "LEFT JOIN questions q ON n.question_id = q.question_id " +
            "WHERE n.note_id = #{noteId} " +
            "AND m.receiver_id = #{userId} " +
            "AND m.message_type = 'SHARE_NOTE' " +
            "AND m.status = 'ACCEPTED' " +
            "LIMIT 1")
    UserStudyNotes findSharedNoteById(@Param("noteId") Long noteId, @Param("userId") Long userId);

    @Select("SELECT n.*, u.username as user_name, q.content as question_title, " +
            "CASE WHEN n.is_public = 1 THEN true ELSE false END as is_shared " +
            "FROM user_study_notes n " +
            "LEFT JOIN users u ON n.user_id = u.user_id " +
            "LEFT JOIN questions q ON n.question_id = q.question_id " +
            "LEFT JOIN note_shares s ON n.note_id = s.note_id " +
            "WHERE s.receiver_id = #{userId} " +
            "ORDER BY s.share_time DESC")
    List<UserStudyNotes> findReceivedSharedNotes(Long userId);

    @Select("SELECT n.*, u.username as user_name, q.content as question_title, " +
            "CASE WHEN n.is_public = 1 THEN true ELSE false END as is_shared " +
            "FROM user_study_notes n " +
            "LEFT JOIN users u ON n.user_id = u.user_id " +
            "LEFT JOIN questions q ON n.question_id = q.question_id " +
            "LEFT JOIN note_shares s ON n.note_id = s.note_id " +
            "WHERE s.sender_id = #{userId} " +
            "ORDER BY s.share_time DESC")
    List<UserStudyNotes> findSentSharedNotes(Long userId);

    @Select("SELECT n.*, u.username as user_name, q.content as question_title, " +
            "CASE WHEN n.is_public = 1 THEN true ELSE false END as is_shared " +
            "FROM user_study_notes n " +
            "LEFT JOIN users u ON n.user_id = u.user_id " +
            "LEFT JOIN questions q ON n.question_id = q.question_id " +
            "ORDER BY n.create_time DESC")
    List<UserStudyNotes> findAllNotes();
} 